import os
import sqlite3
from dataclasses import dataclass
from typing import Iterable, List, Optional, Sequence, Set, Tuple


def ensure_parent_dir(path: str) -> None:
    parent = os.path.dirname(path)
    if parent and not os.path.exists(parent):
        os.makedirs(parent, exist_ok=True)


def get_conn(db_path: str) -> sqlite3.Connection:
    ensure_parent_dir(db_path)
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON;")
    return conn


def init_db(db_path: str) -> None:
    conn = get_conn(db_path)
    with conn:
        conn.executescript(
            """
            CREATE TABLE IF NOT EXISTS pdfs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                doc_id TEXT,
                alt_fingerprint TEXT,
                page_count INTEGER,
                first_page_text_sha1 TEXT,
                file_size INTEGER,
                file_mtime REAL,
                description TEXT,
                created_at TEXT DEFAULT (datetime('now')),
                updated_at TEXT DEFAULT (datetime('now'))
            );

            CREATE UNIQUE INDEX IF NOT EXISTS idx_pdfs_doc_id ON pdfs(doc_id) WHERE doc_id IS NOT NULL;
            CREATE INDEX IF NOT EXISTS idx_pdfs_alt ON pdfs(alt_fingerprint);

            CREATE TABLE IF NOT EXISTS paths (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                pdf_id INTEGER NOT NULL,
                path TEXT NOT NULL,
                is_current INTEGER NOT NULL DEFAULT 1,
                last_seen TEXT DEFAULT (datetime('now')),
                FOREIGN KEY(pdf_id) REFERENCES pdfs(id) ON DELETE CASCADE
            );
            CREATE UNIQUE INDEX IF NOT EXISTS idx_paths_unique ON paths(pdf_id, path);
            CREATE INDEX IF NOT EXISTS idx_paths_current ON paths(is_current);

            CREATE TABLE IF NOT EXISTS tags (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL UNIQUE
            );

            CREATE TABLE IF NOT EXISTS pdf_tags (
                pdf_id INTEGER NOT NULL,
                tag_id INTEGER NOT NULL,
                PRIMARY KEY (pdf_id, tag_id),
                FOREIGN KEY(pdf_id) REFERENCES pdfs(id) ON DELETE CASCADE,
                FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE
            );
            """
        )
    conn.close()


@dataclass
class PdfIdentity:
    doc_id: Optional[str]
    alt_fingerprint: str
    page_count: int
    first_page_text_sha1: Optional[str]
    file_size: int
    file_mtime: float


def _find_pdf_by_identity(conn: sqlite3.Connection, ident: PdfIdentity) -> Optional[int]:
    if ident.doc_id:
        row = conn.execute("SELECT id FROM pdfs WHERE doc_id = ?", (ident.doc_id,)).fetchone()
        if row:
            return int(row[0])
    # Weak fingerprint fallback: page_count + first_page_text_sha1
    if ident.first_page_text_sha1:
        row = conn.execute(
            "SELECT id FROM pdfs WHERE page_count = ? AND first_page_text_sha1 = ?",
            (ident.page_count, ident.first_page_text_sha1),
        ).fetchone()
        if row:
            return int(row[0])
    return None


def upsert_pdf(conn: sqlite3.Connection, ident: PdfIdentity, description: Optional[str] = None) -> int:
    existing_id = _find_pdf_by_identity(conn, ident)
    if existing_id is not None:
        conn.execute(
            """
            UPDATE pdfs SET
                doc_id = COALESCE(?, doc_id),
                alt_fingerprint = ?,
                page_count = ?,
                first_page_text_sha1 = COALESCE(?, first_page_text_sha1),
                file_size = ?,
                file_mtime = ?,
                description = COALESCE(?, description),
                updated_at = datetime('now')
            WHERE id = ?
            """,
            (
                ident.doc_id,
                ident.alt_fingerprint,
                ident.page_count,
                ident.first_page_text_sha1,
                ident.file_size,
                ident.file_mtime,
                description,
                existing_id,
            ),
        )
        return existing_id

    cur = conn.execute(
        """
        INSERT INTO pdfs (doc_id, alt_fingerprint, page_count, first_page_text_sha1, file_size, file_mtime, description)
        VALUES (?, ?, ?, ?, ?, ?, ?)
        """,
        (
            ident.doc_id,
            ident.alt_fingerprint,
            ident.page_count,
            ident.first_page_text_sha1,
            ident.file_size,
            ident.file_mtime,
            description,
        ),
    )
    return int(cur.lastrowid)


def add_or_update_path(conn: sqlite3.Connection, pdf_id: int, path: str) -> None:
    # Mark all paths of this pdf as not current, then upsert the given path as current
    with conn:
        conn.execute("UPDATE paths SET is_current = 0 WHERE pdf_id = ?", (pdf_id,))
        conn.execute(
            """
            INSERT INTO paths (pdf_id, path, is_current)
            VALUES (?, ?, 1)
            ON CONFLICT(pdf_id, path) DO UPDATE SET
                is_current = 1,
                last_seen = datetime('now')
            """,
            (pdf_id, path),
        )


def get_or_create_tag(conn: sqlite3.Connection, name: str) -> int:
    row = conn.execute("SELECT id FROM tags WHERE name = ?", (name,)).fetchone()
    if row:
        return int(row[0])
    cur = conn.execute("INSERT INTO tags (name) VALUES (?)", (name,))
    return int(cur.lastrowid)


def set_tags_for_pdf(conn: sqlite3.Connection, pdf_id: int, tag_names: Sequence[str]) -> None:
    tag_ids: List[int] = [get_or_create_tag(conn, n) for n in tag_names]
    with conn:
        conn.execute("DELETE FROM pdf_tags WHERE pdf_id = ?", (pdf_id,))
        conn.executemany(
            "INSERT OR IGNORE INTO pdf_tags (pdf_id, tag_id) VALUES (?, ?)",
            [(pdf_id, tid) for tid in tag_ids],
        )


def add_tags_for_pdf(conn: sqlite3.Connection, pdf_id: int, tag_names: Sequence[str]) -> None:
    tag_ids: List[int] = [get_or_create_tag(conn, n) for n in tag_names]
    with conn:
        conn.executemany(
            "INSERT OR IGNORE INTO pdf_tags (pdf_id, tag_id) VALUES (?, ?)",
            [(pdf_id, tid) for tid in tag_ids],
        )


def remove_tags_for_pdf(conn: sqlite3.Connection, pdf_id: int, tag_names: Sequence[str]) -> None:
    tag_ids: List[int] = []
    for n in tag_names:
        row = conn.execute("SELECT id FROM tags WHERE name = ?", (n,)).fetchone()
        if row:
            tag_ids.append(int(row[0]))
    if not tag_ids:
        return
    with conn:
        conn.executemany(
            "DELETE FROM pdf_tags WHERE pdf_id = ? AND tag_id = ?",
            [(pdf_id, tid) for tid in tag_ids],
        )


def delete_tag(conn: sqlite3.Connection, tag_name: str) -> bool:
    """Delete a tag and remove it from all associated PDFs.

    Returns True if the tag existed and was removed."""
    row = conn.execute("SELECT id FROM tags WHERE name = ?", (tag_name,)).fetchone()
    if not row:
        return False
    tag_id = int(row[0])
    with conn:
        conn.execute("DELETE FROM pdf_tags WHERE tag_id = ?", (tag_id,))
        conn.execute("DELETE FROM tags WHERE id = ?", (tag_id,))
    return True


def list_all_tags_with_counts(conn: sqlite3.Connection) -> List[sqlite3.Row]:
    return conn.execute(
        """
        SELECT t.name as name, COUNT(pt.pdf_id) as cnt
        FROM tags t
        LEFT JOIN pdf_tags pt ON pt.tag_id = t.id
        GROUP BY t.id
        ORDER BY t.name COLLATE NOCASE
        """
    ).fetchall()


def list_pdfs(conn: sqlite3.Connection, limit: int = 500) -> List[sqlite3.Row]:
    return conn.execute(
        """
        SELECT p.id, p.page_count, p.file_size, p.file_mtime, p.description,
               (SELECT path FROM paths WHERE pdf_id = p.id AND is_current = 1 LIMIT 1) AS path,
               GROUP_CONCAT(t.name, ', ') AS tags
        FROM pdfs p
        LEFT JOIN pdf_tags pt ON pt.pdf_id = p.id
        LEFT JOIN tags t ON t.id = pt.tag_id
        GROUP BY p.id
        ORDER BY p.updated_at DESC
        LIMIT ?
        """,
        (limit,),
    ).fetchall()


def get_pdf_tags(conn: sqlite3.Connection, pdf_id: int) -> List[str]:
    rows = conn.execute(
        """
        SELECT t.name
        FROM tags t
        JOIN pdf_tags pt ON pt.tag_id = t.id
        WHERE pt.pdf_id = ?
        ORDER BY t.name COLLATE NOCASE
        """,
        (pdf_id,),
    ).fetchall()
    return [str(r[0]) for r in rows]


def get_pdf_by_id(conn: sqlite3.Connection, pdf_id: int) -> Optional[sqlite3.Row]:
    return conn.execute(
        "SELECT * FROM pdfs WHERE id = ?",
        (pdf_id,),
    ).fetchone()


def list_pdf_paths(conn: sqlite3.Connection, pdf_id: int) -> List[str]:
    rows = conn.execute(
        "SELECT path FROM paths WHERE pdf_id = ? ORDER BY last_seen DESC",
        (pdf_id,),
    ).fetchall()
    return [str(r[0]) for r in rows]


def query_pdf_ids_by_tag(conn: sqlite3.Connection, tag_name: str) -> Set[int]:
    rows = conn.execute(
        """
        SELECT pt.pdf_id
        FROM tags t
        JOIN pdf_tags pt ON pt.tag_id = t.id
        WHERE t.name = ?
        """,
        (tag_name,),
    ).fetchall()
    return {int(r[0]) for r in rows}


def query_pdfs_by_ids(conn: sqlite3.Connection, ids: Iterable[int]) -> List[sqlite3.Row]:
    id_list = list(ids)
    if not id_list:
        return []
    placeholders = ",".join(["?"] * len(id_list))
    sql = f"""
        SELECT p.id, p.page_count, p.file_size, p.file_mtime, p.description,
               (SELECT path FROM paths WHERE pdf_id = p.id AND is_current = 1 LIMIT 1) AS path,
               GROUP_CONCAT(t.name, ', ') AS tags
        FROM pdfs p
        LEFT JOIN pdf_tags pt ON pt.pdf_id = p.id
        LEFT JOIN tags t ON t.id = pt.tag_id
        WHERE p.id IN ({placeholders})
        GROUP BY p.id
        ORDER BY p.updated_at DESC
    """
    return conn.execute(sql, id_list).fetchall()


def update_pdf_description(conn: sqlite3.Connection, pdf_id: int, description: Optional[str]) -> None:
    """更新 PDF 描述"""
    conn.execute(
        "UPDATE pdfs SET description = ?, updated_at = datetime('now') WHERE id = ?",
        (description, pdf_id),
    )


